from datetime import datetime, timedelta
import pandas as pd
import pymongo
from dateutil import tz
from zc_core.client.mongo_client import Mongo
from zc_core.util.batch_gen import batch_to_year, time_to_batch_no
from zc_core.util.common import format_time

order_map = [
    {'col': '_id', 'title': '订单编号'},
    {'col': 'status', 'title': '订单状态'},
    {'col': 'totalPrice', 'title': '总金额'},
    {'col': 'orderTime', 'title': '下单时间'},
    {'col': 'orderUser', 'title': '收货人'},
    {'col': 'orderAddr', 'title': '收货地址'},
    {'col': 'spOrderCode', 'title': '供应商订单编号'},
    {'col': 'receiptCode', 'title': '验收单号'},
    {'col': 'tradeType', 'title': '交易类型'},
    {'col': 'clientType', 'title': '订单来源'},
    {'col': 'cancelReason', 'title': '失败原因'},
]

order_item_map = [
    {'col': '_id', 'title': '订单编号'},
    {'col': 'status', 'title': '订单状态'},
    {'col': 'orderTime', 'title': '下单时间'},
    {'col': 'skuId', 'title': '商品编号'},
    {'col': 'skuName', 'title': '商品名称'},
    {'col': 'count', 'title': '商品数量'},
    {'col': 'amount', 'title': '小计金额'},
    {'col': 'totalPrice', 'title': '总金额'},
    {'col': 'orderUser', 'title': '收货人'},
    {'col': 'orderAddr', 'title': '收货地址'},
    {'col': 'spOrderCode', 'title': '供应商订单编号'},
    {'col': 'receiptCode', 'title': '验收单号'},
    {'col': 'tradeType', 'title': '交易类型'},
    {'col': 'clientType', 'title': '订单来源'},
    {'col': 'cancelReason', 'title': '失败原因'},
]


def export_item_data(batch_no, day_range, file):
    deadline = datetime.now(tz.gettz('Asia/Shanghai')) - timedelta(days=day_range)
    order_columns = list()
    order_header = list()
    for row in order_map:
        order_columns.append(row.get('col'))
        order_header.append(row.get('title'))

    item_columns = list()
    item_header = list()
    for row in order_item_map:
        item_columns.append(row.get('col'))
        item_header.append(row.get('title'))

    order_list = []
    order_item_list = []

    mongo = Mongo()
    orders = mongo.list('order_{}'.format(batch_to_year(batch_no)), query={'orderTime': {'$gte': deadline}}, sort=[("orderTime", pymongo.DESCENDING)])
    for row in orders:
        order = dict()
        order_time = row.get('orderTime') + timedelta(hours=8)
        order['_id'] = row.get('_id')
        order['status'] = row.get('status')
        order['orderUser'] = row.get('orderUser')
        order['orderTime'] = format_time(order_time)
        order['totalPrice'] = row.get('totalPrice')
        order['orderAddr'] = row.get('orderAddr')
        order['spOrderCode'] = row.get('spOrderCode')
        order['receiptCode'] = row.get('receiptCode')
        order['tradeType'] = row.get('tradeType')
        order['clientType'] = row.get('clientType')
        order['cancelReason'] = row.get('cancelReason')
        order_list.append(order)

        item_list = row.get('itemList', [])
        for item_row in item_list:
            item = dict()
            item['_id'] = row.get('_id')
            item['status'] = row.get('status')
            item['orderTime'] = format_time(order_time)
            item['skuId'] = item_row.get('skuId')
            item['skuName'] = item_row.get('skuName')
            item['count'] = item_row.get('count')
            item['amount'] = item_row.get('amount')
            item['orderUser'] = row.get('orderUser')
            item['totalPrice'] = row.get('totalPrice')
            item['orderAddr'] = row.get('orderAddr')
            item['spOrderCode'] = row.get('spOrderCode')
            item['receiptCode'] = row.get('receiptCode')
            item['tradeType'] = row.get('tradeType')
            item['clientType'] = row.get('clientType')
            item['cancelReason'] = row.get('cancelReason')
            item['url'] = item_row.get('url')
            order_item_list.append(item)

    write = pd.ExcelWriter(file)
    df1 = pd.DataFrame(order_list)
    df1.to_excel(write, sheet_name='主订单', columns=order_columns, header=order_header, index=False)
    df2 = pd.DataFrame(order_item_list)
    df2.to_excel(write, sheet_name='订单明细', columns=item_columns, header=item_header, index=False)
    write.save()


if __name__ == '__main__':
    batch_no = time_to_batch_no(datetime.now(), delta=0)
    export_item_data(batch_no, 12, 'G:\\得力军网近10日订单_{}.xls'.format(batch_no))
